/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ntb.models.impl;

import com.ntb.entities.Buildings;
import com.ntb.common.DatabaseConnection;
import com.ntb.entities.BuildingStatus;
import com.ntb.models.BuildingStatusDAO;
import com.ntb.models.BuildingsDAO;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;

/**
 *
 * @author DATBP
 */
public class BuildingsDAOImpl extends DatabaseConnection implements BuildingsDAO {

    public Iterator getCountList(int year) {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_countBuildingsMonth(?)}");
            cst.setInt(1, year);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                list.add(new int[]{rs.getInt(1), rs.getInt(2)});
            }
            closeConnection();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public int countAll() {
        try {
            openConnection();
            ResultSet rs = getResultset("Select Count(BuildingID) from Buildings");
            int result = 0;
            if (rs.next()) {
                result = rs.getInt(1);
            }
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public Iterator<Buildings> getList() {
        try {
            ArrayList arr = new ArrayList();
            openConnection();
            ResultSet rs = getResultset("Select * from Buildings");
            while (rs.next()) {
                Buildings building = new Buildings();
                building.setBuildingID(rs.getInt("BuildingID"));
                building.setLandID(rs.getInt("LandID"));
                building.setBuildingTypeID(rs.getInt("BuildingTypeID"));
                building.setBuildingName(rs.getString("BuildingName"));
                building.setNumFloors(rs.getInt("NumFloors"));
                building.setNumRooms(rs.getInt("NumRooms"));
                building.setConstructedDate(rs.getDate("ConstructedDate"));
                building.setDescription(rs.getString("Description"));
                building.setStatusCode(rs.getString("StatusCode"));
                building.setStatusChangedDate(rs.getTimestamp("StatusChangedDate"));
                building.setOccupancyPermitID(rs.getInt("OccupancyPermitID"));
                arr.add(building);
            }
            closeConnection();
            return arr.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public Iterator<Buildings> getList(String buildingId, String buildingName, String landId, int buildingTypeId, int floorFrom, int floorTo, int roomFrom, int roomTo, String constructedDateFrom, String constructedDateTo, String status, int page) {
        try {
            ArrayList arr = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_pageBuildings(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            cst.setString(1, buildingId);
            cst.setString(2, buildingName);
            cst.setString(3, landId);
            cst.setInt(4, buildingTypeId);
            cst.setInt(5, floorFrom);
            cst.setInt(6, floorTo);
            cst.setInt(7, roomFrom);
            cst.setInt(8, roomTo);
            cst.setString(9, constructedDateFrom);
            cst.setString(10, constructedDateTo);
            cst.setString(11, status);
            cst.setInt(12, page);
            cst.setInt(13, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                Buildings building = new Buildings();
                building.setBuildingID(rs.getInt("BuildingID"));
                building.setLandID(rs.getInt("LandID"));
                building.setBuildingTypeID(rs.getInt("BuildingTypeID"));
                building.setTypeName(rs.getString("TypeName"));
                building.setBuildingName(rs.getString("BuildingName"));
                building.setNumFloors(rs.getInt("NumFloors"));
                building.setNumRooms(rs.getInt("NumRooms"));
                building.setConstructedDate(rs.getDate("ConstructedDate"));
                building.setStatusCode(rs.getString("StatusCode"));
                arr.add(building);
            }
            closeConnection();
            if (arr.size() > 0) {
                return arr.iterator();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public int getPageCount(String buildingId, String buildingName, String landId, int buildingTypeId, int floorFrom, int floorTo, int roomFrom, int roomTo, String constructedDateFrom, String constructedDateTo, String status) {
        try {
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_countPageBuildings(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            cst.setString(1, buildingId);
            cst.setString(2, buildingName);
            cst.setString(3, landId);
            cst.setInt(4, buildingTypeId);
            cst.setInt(5, floorFrom);
            cst.setInt(6, floorTo);
            cst.setInt(7, roomFrom);
            cst.setInt(8, roomTo);
            cst.setString(9, constructedDateFrom);
            cst.setString(10, constructedDateTo);
            cst.setString(11, status);
            cst.setInt(12, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt("Count");
            }
            closeConnection();

            return count;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public Buildings getRow(int id) {
        try {
            ArrayList arr = new ArrayList();
            openConnection();
            Buildings building = new Buildings();
            PreparedStatement pst = conn.prepareStatement("Select Buildings.*, TypeName from Buildings " +
                    "LEFT JOIN BuildingTypes " +
                    "ON Buildings.BuildingTypeID = BuildingTypes.BuildingTypeID Where BuildingID=?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                building.setBuildingID(rs.getInt("BuildingID"));
                building.setLandID(rs.getInt("LandID"));
                building.setBuildingTypeID(rs.getInt("BuildingTypeID"));
                building.setTypeName(rs.getString("TypeName"));
                building.setBuildingName(rs.getString("BuildingName"));
                building.setNumFloors(rs.getInt("NumFloors"));
                building.setNumRooms(rs.getInt("NumRooms"));
                building.setConstructedDate(rs.getDate("ConstructedDate"));
                building.setDescription(rs.getString("Description"));
                building.setStatusCode(rs.getString("StatusCode"));
                building.setStatusChangedDate(rs.getTimestamp("StatusChangedDate"));
                building.setOccupancyPermitID(rs.getInt("OccupancyPermitID"));
                arr.add(building);
            }
            closeConnection();
            return building;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public boolean checkDuplicateName(String name) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select * From Buildings Where BuildingName=?");
            pst.setString(1, name);
            ResultSet rs = pst.executeQuery();
            boolean result = rs.next();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public boolean checkExist(int buildingId) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select BuildingID From Buildings Where BuildingID=?");
            pst.setInt(1, buildingId);
            ResultSet rs = pst.executeQuery();
            boolean result = rs.next();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public int add(Buildings object) {
        try {
            openConnection();
            String sql = "Insert Buildings(BuildingName, LandID, BuildingTypeID, NumFloors, NumRooms, " +
                    "ConstructedDate, Description, StatusCode, StatusChangedDate) " +
                    "Values(?, ?, ?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pst.setString(1, object.getBuildingName());
            pst.setInt(2, object.getLandID());
            pst.setInt(3, object.getBuildingTypeID());
            pst.setInt(4, object.getNumFloors());
            pst.setInt(5, object.getNumRooms());

            pst.setDate(6, object.getConstructedDate());
            pst.setString(7, object.getDescription());
            pst.setString(8, "CR");

            Timestamp date = new Timestamp(new java.util.Date().getTime());
            pst.setTimestamp(9, date);

            int result = pst.executeUpdate();

            ResultSet rs = pst.getGeneratedKeys();
            rs.next();

            BuildingStatusDAO statusDao = new BuildingStatusDAOImpl();
            BuildingStatus status = new BuildingStatus();
            status.setBuildingID(rs.getInt(1));
            status.setChangedDate(date);
            status.setStatusCode("CR");
            statusDao.add(status);

            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int update(Buildings object) {
        try {
            openConnection();
            String sql = "update Buildings set BuildingName = ?, BuildingTypeID = ?, NumFloors = ?, NumRooms = ?, " +
                    "ConstructedDate = ?, Description=? " +
                    "where BuildingID=?";
            PreparedStatement pst = conn.prepareStatement(sql);

            pst.setString(1, object.getBuildingName());
            pst.setInt(2, object.getBuildingTypeID());
            pst.setInt(3, object.getNumFloors());
            pst.setInt(4, object.getNumRooms());

            pst.setDate(5, object.getConstructedDate());
            pst.setString(6, object.getDescription());

            pst.setInt(7, object.getBuildingID());
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int updateStatus(int buildingID, String statusCode) {
        try {
            BuildingStatusDAO statusDao = new BuildingStatusDAOImpl();
            BuildingStatus status = new BuildingStatus();
            status.setBuildingID(buildingID);
            status.setChangedDate(new Timestamp(new java.util.Date().getTime()));
            status.setStatusCode(statusCode);
            if (statusDao.add(status) > 0) {
                openConnection();
                PreparedStatement pst = conn.prepareStatement("Update Buildings Set StatusCode = ?, StatusChangedDate = ? " +
                        "Where BuildingID=?");
                pst.setString(1, statusCode);
                pst.setTimestamp(2, status.getChangedDate());
                pst.setInt(3, buildingID);

                int result = pst.executeUpdate();
                closeConnection();
                return result;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int updateOccupancyPermitID(int buildingID, int occupancyPermitID) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Update Buildings set OccupancyPermitID = ? " +
                    "Where BuildingID=?");
            pst.setInt(1, occupancyPermitID);
            pst.setInt(2, buildingID);

            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int delete(int id) {
        try {
            openConnection();
            String sql = "delete Buildings where BuildingID=? " +
                    "AND ? NOT IN (SELECT BuildingID FROM Flats)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            pst.setInt(2, id);
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int deleteIn(String arrayIds) {
        try {
            openConnection();
            String sql = "delete Buildings where BuildingID in (" + arrayIds + ")";
            Statement st = conn.createStatement();
            int rs = st.executeUpdate(sql);
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }
}
